package com.crawl.zhihu.dao;


import com.crawl.core.dao.ConnectionManager;
import com.crawl.view.TopVO;
import com.crawl.zhihu.entity.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class ZhiHuDaoImp implements ZhiHuDao {
    private static Logger logger = LoggerFactory.getLogger(ZhiHuDao.class);

    /**
     * 初始化table
     */
    public static void DBTablesInit() {
        ResultSet rs = null;
        //创建properties文件对象
        Properties p = new Properties();
        //获取一个数据库连接
        Connection cn = ConnectionManager.getConnection();
        try {
            //加载config.properties文件
            p.load(ZhiHuDaoImp.class.getResourceAsStream("/config.properties"));
            //查询url table
            rs = cn.getMetaData().getTables(null, null, "url", null);
            //创建数据库Statement对象
            Statement st = cn.createStatement();
            if(!rs.next()){
                //不存在url表，创建url表
                st.execute(p.getProperty("createUrlTable"));
                logger.info("url表创建成功");
            }
            else{
                logger.info("url表已存在");
            }
            //查询user table
            rs = cn.getMetaData().getTables(null, null, "user", null);
            if(!rs.next()){
                 //不存在user表，创建user表
                st.execute(p.getProperty("createUserTable"));
                logger.info("user表创建成功");
            }
            else{
                logger.info("user表已存在");
            }
            //关闭数据库结果集
            rs.close();
            //关闭数据库操作对象
            st.close();
            //关闭连接
            cn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



    /**
     * 根据userToken查询数据库是否存在该用户
     * @param userToken 用户token，唯一标识
     * @return 查询结果
     */
    @Override
    public boolean isExistUser(String userToken) {
        //调用本类中的方法
        return isExistUser(ConnectionManager.getConnection(), userToken);
    }

    /**
     * 根据userToken查询数据库是否存在该用户
     * @param cn 数据库连接
     * @param userToken 用户token，唯一标识
     * @return
     */
    @Override
    public boolean isExistUser(Connection cn, String userToken) {
        //查询sql
        String isContainSql = "select count(*) from user WHERE user_token='" + userToken + "'";
        try {
            if(isExistRecord(isContainSql)){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据查询语句，判断是否存在查询结果
     * @param sql 查询语句
     * @return
     * @throws SQLException
     */
    @Override
    public boolean isExistRecord(String sql) throws SQLException{
        //调用本类中的方法
        return isExistRecord(ConnectionManager.getConnection(), sql);
    }

    /**
     * 根据查询语句，判断是否存在查询结果
     * @param cn 数据库连接
     * @param sql 查询语句
     * @return
     * @throws SQLException
     */
    @Override
    public boolean isExistRecord(Connection cn, String sql) throws SQLException {
        int num = 0;
        PreparedStatement pstmt;
        //创建PreparedStatement对象
        pstmt = cn.prepareStatement(sql);
        //执行查询，并返回结果
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
            num = rs.getInt("count(*)");
        }
        //关闭ResultSet对象
        rs.close();
        //关闭PreparedStatement对象
        pstmt.close();
        if(num == 0){
            return false;
        }else{
            return true;
        }
    }
    @Override
    public boolean insertUser(User u) {
        return insertUser(ConnectionManager.getConnection(), u);
    }

    /**
     * insert user资料至数据库
     * @param cn 数据库连接
     * @param u 用户对象
     * @return insert操作结果
     */
    @Override
    public boolean insertUser(Connection cn, User u) {
        try {
            //判断数据库是否存在该用户，存在则直接返回false
            if (isExistUser(cn, u.getUserToken())){
                return false;
            }
            //创建insert sql 语句
            String column = "location,business,sex,employment,username,url,agrees,thanks,asks," +
                    "answers,posts,followees,followers,hashId,education,user_token";
            String values = "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?";
            String sql = "insert into user (" + column + ") values(" +values+")";
            PreparedStatement pstmt;
            //根据数据库连接创建PreparedStatement对象
            pstmt = cn.prepareStatement(sql);
            //设置用户所在位置
            pstmt.setString(1,u.getLocation());
            //所在行业
            pstmt.setString(2,u.getBusiness());
            //性别
            pstmt.setString(3,u.getSex());
            //所在企业
            pstmt.setString(4,u.getEmployment());
            //用户名
            pstmt.setString(5,u.getUsername());
            //知乎主页url
            pstmt.setString(6,u.getUrl());
            //获得的赞同数
            pstmt.setInt(7,u.getAgrees());
            //获得的感谢数
            pstmt.setInt(8,u.getThanks());
            //提问数
            pstmt.setInt(9,u.getAsks());
            //回答数
            pstmt.setInt(10,u.getAnswers());
            //文章数
            pstmt.setInt(11,u.getPosts());
            //粉丝数量
            pstmt.setInt(12,u.getFollowees());
            //关注人数
            pstmt.setInt(13,u.getFollowers());
            //hashId,用户唯一标示
            pstmt.setString(14,u.getHashId());
            //教育
            pstmt.setString(15,u.getEducation());
            //用户token，用户唯一标示
            pstmt.setString(16,u.getUserToken());
            pstmt.executeUpdate();
            pstmt.close();
            logger.info("插入数据库成功---" + u.getUsername());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        return true;
    }

    @Override
    public boolean insertUrl(Connection cn, String md5Url) {
        String isContainSql = "select count(*) from url WHERE md5_url ='" + md5Url + "'";
        try {
            if(isExistRecord(cn, isContainSql)){
                logger.debug("数据库已经存在该url---" + md5Url);
                return false;
            }
            String sql = "insert into url (md5_url) values( ?)";
            PreparedStatement pstmt;
            pstmt = cn.prepareStatement(sql);
            pstmt.setString(1,md5Url);
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        logger.debug("url插入成功---");
        return true;
    }

    @Override
    public List<User> queryUserList(String sql) {
        Connection connection = ConnectionManager.getConnection();
        List<User> userList = new ArrayList<>();
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                User user = new User();
                //location,business,sex,employment,username,url,agrees,thanks,asks," +
                //"answers,posts,followees,followers,hashId,education,user_token
                user.setLocation(resultSet.getString("location"));
                user.setBusiness(resultSet.getString("business"));
                user.setSex(resultSet.getString("sex"));
                user.setEmployment(resultSet.getString("employment"));
                user.setUsername(resultSet.getString("username"));
                user.setUrl(resultSet.getString("url"));
                user.setAgrees(resultSet.getInt("agrees"));
                user.setThanks(resultSet.getInt("thanks"));
                user.setAsks(resultSet.getInt("asks"));
                user.setAnswers(resultSet.getInt("answers"));
                user.setPosts(resultSet.getInt("posts"));
                user.setFollowees(resultSet.getInt("followees"));
                user.setFollowers(resultSet.getInt("followers"));
                user.setHashId(resultSet.getString("hashId"));
                user.setEducation(resultSet.getString("education"));
                user.setUserToken(resultSet.getString("user_token"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public List<TopVO> queryTopList(String sql) {
        Connection connection = ConnectionManager.getConnection();
        List<TopVO> topVOList = new ArrayList<>();
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                TopVO topVO = new TopVO();
                topVO.setName(resultSet.getString("name"));
                topVO.setCount(resultSet.getInt("count"));
                topVOList.add(topVO);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return topVOList;
    }
}
